from ctypes.wintypes import DOUBLE
from sqlalchemy import create_engine, Column, Float, String, Integer, MetaData, Table, Double,VARCHAR
import pandas as pd
import uuid
import numpy as np
from flask import Flask, request, jsonify

app = Flask(__name__)

username = 'root'
password = '123456'
host = '192.168.0.189'
port = '3306'
database = 'shale-gas'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')

def calculate_annual_total_cost(total_cost_df, construct_cost_df, year_production_df):
    # Melt the well_df to have a long format for year-wise production
    year_production_df = year_production_df.melt(id_vars=['well_no'], var_name='year', value_name='year_production')
    year_production_df['year'] = year_production_df['year'].astype(int)

    # 提取平台编号
    def extract_platform_no(well_no):
        if '-' in well_no:
            return well_no.split('-')[0]
        else:
            return well_no

    year_production_df['platform_no'] = year_production_df['well_no'].apply(extract_platform_no)
    year_production_df = year_production_df.drop_duplicates(subset=['platform_no', 'year', 'well_no'])

    # Append well numbers to the platform DataFrame by matching platform_no
    total_cost_df = total_cost_df.merge(year_production_df[['platform_no', 'well_no', 'year', 'year_production']], on=['platform_no', 'year'], how='left')
    total_cost_df = total_cost_df.drop_duplicates(subset=['platform_no', 'year', 'well_no'])
    platform_year_total_production = total_cost_df.groupby(['platform_no', 'year'])['year_production'].sum().reset_index()
    platform_year_total_production = platform_year_total_production.rename(columns={'year_production': 'total_platform_production'})

    # 将平台和年份的总产气量合并回原数据
    total_cost_df = pd.merge(total_cost_df, platform_year_total_production, on=['platform_no', 'year'], how='left')
    total_cost_df['well_ratio'] = total_cost_df['year_production'] / total_cost_df['total_platform_production']
    total_cost_df['well_total_cost'] = total_cost_df['well_ratio'] * total_cost_df['total_cost_platform']
    total_cost_df['well_depreciation'] = total_cost_df['well_ratio'] * total_cost_df['depreciation_platform']
    total_cost_df['well_difference_td'] = total_cost_df['well_total_cost'] - total_cost_df['well_depreciation']

    # 合并建井成本数据
    total_cost_df = total_cost_df.merge(construct_cost_df[['well_no', 'construct_year', 'well_type', 'core_area', 'construct_cost']], on='well_no', how='left')
    # Remove rows where the well's year of production is earlier than the well's construction year
    total_cost_df = total_cost_df[total_cost_df['year'] >= total_cost_df['construct_year']]
    total_cost_df = total_cost_df.drop_duplicates()

    return total_cost_df

def generate_cashflow_out(platform_data):
    cashflow_data = pd.DataFrame()
    cashflow_data["well_no"] = platform_data["well_no"].unique()
    cashflow_data["construct_year"] = platform_data.drop_duplicates(subset="well_no")["construct_year"].values
    cashflow_data["construct_cost_year"] = cashflow_data["construct_year"] - 1
    cashflow_data = cashflow_data.merge(
        platform_data[['well_no', 'construct_cost', 'well_type', 'core_area']].drop_duplicates(), on='well_no',
        how='left')

    start_time = platform_data["year"].min() - 1
    end_time = platform_data["year"].max()


    # 初始化每年的现金流数据为0
    years = [str(year) for year in range(start_time, end_time + 1)]
    for year in years:
        cashflow_data[year] = 0.0

    # 使用平台数据中的well_difference_td来逐年填充现金流数据
    for i, row in platform_data.iterrows():
        well_no = row["well_no"]
        well_difference_td = row["well_difference_td"]
        year = row["year"]
        if str(year) in cashflow_data.columns:
            cashflow_data.loc[cashflow_data["well_no"] == well_no, str(year)] = well_difference_td

    # 将construct_cost填写到construct_cost_year对应的year中
    for i, row in platform_data.iterrows():
        well_no = row["well_no"]
        construct_year = int(row["construct_year"])-1  # 确保construct_year为整数类型
        construct_year_str = str(construct_year)
        if construct_year_str in cashflow_data.columns:
            construct_cost = row.get("construct_cost", 0)  # 使用construct_cost列
            if pd.notna(construct_cost):
                cashflow_data.loc[cashflow_data["well_no"] == well_no, construct_year_str] = construct_cost

    return cashflow_data

def calculate_present_cashoutflow(generated_cashflow_out, cash_outflow_years, discount_rate, current_year):
    for year in cash_outflow_years:
        cash_outflow_column = f'cash_outflow{year}'
        generated_cashflow_out[f'present_value_out{year}'] = generated_cashflow_out[cash_outflow_column] * ((1 + discount_rate) ** (current_year - year))
    return generated_cashflow_out

def calculate_cash_income(production_df, gas_prices_df, discount_rate, current_year,future_year=2028):
    # Convert the production data from wide format (with year columns) to long format
    production_long_df = pd.melt(production_df, id_vars=['well_no'], var_name='year', value_name='production')

    # Convert the year from string to integer, if necessary
    production_long_df['year'] = production_long_df['year'].astype(int)

    # Ensure that gas_prices_df contains both 'year' and 'after-tax prices' columns
    if 'year' not in gas_prices_df.columns or 'after_tax_prices' not in gas_prices_df.columns:
        raise ValueError("The gas_price table must contain 'year' and 'after-tax prices' columns.")

    # Separate 'Future' price and other years
    future_price = gas_prices_df.loc[gas_prices_df['year'] == 'Future', 'after_tax_prices'].values[0]

    # Filter out Future row for the regular years
    gas_prices_df = gas_prices_df.loc[gas_prices_df['year'] != 'Future'].copy()

    # Convert the remaining years to integer and create a dictionary for easier lookup
    gas_prices_df['year'] = gas_prices_df['year'].astype(int)
    gas_prices = dict(zip(gas_prices_df['year'], gas_prices_df['after_tax_prices']))

    # Function to get the correct gas price based on the year
    def get_gas_price(year,future_year=2028):
        # If year >= 2028, return the future price; otherwise, return the year's price or fallback to future price
        if year >= future_year:
            return future_price
        return gas_prices.get(year, future_price)

    # Apply the function to assign gas prices to each row in the production DataFrame
    production_long_df['Gas Price'] = production_long_df['year'].apply(get_gas_price)

    # Calculate the cash flow (production * gas price)
    production_long_df['cash flow income'] = production_long_df['production'] * production_long_df['Gas Price']
    production_long_df['present value of cash flow Income'] = production_long_df.apply(
        lambda row: row['cash flow income'] * (1 + discount_rate) ** (current_year - row['year']), axis=1)

    return production_long_df

def combine_data(present_cashoutflow, present_cashincome):
    # 获取收入数据年份
    years = present_cashincome['year'].unique()
    # 创建合并后的利润 DataFrame
    combine_profit_df = present_cashoutflow.copy()
    income_df = present_cashincome.copy()

    # 合并收入数据
    for _, row in income_df.iterrows():
        well_no = row['well_no']
        year = row['year']
        income_year_column = f'cash_income{year}'
        present_value_year_column = f'present_value_income{year}'
        if income_year_column not in combine_profit_df.columns:
            combine_profit_df[income_year_column] = None
        if present_value_year_column not in combine_profit_df.columns:
            combine_profit_df[present_value_year_column] = None
        combine_profit_df.loc[combine_profit_df['well_no'] == well_no, income_year_column] = row['cash flow income']
        combine_profit_df.loc[combine_profit_df['well_no'] == well_no, present_value_year_column] = row['present value of cash flow Income']

    numeric_cols =  combine_profit_df.select_dtypes(include=['number']).columns
    combine_profit_df[numeric_cols] =  combine_profit_df[numeric_cols].round(8)

    return combine_profit_df

def calculate_net_present_value(data):
    df = pd.DataFrame(data)

    # 创建一个新的DataFrame存储净现值
    net_present_value = {}

    # 自动检测年份并计算每年的净现值（收入 - 支出）
    all_years = sorted(set(int(col[-4:]) for col in df.columns if 'present_value' in col))
    min_year = min(all_years)
    max_year = max(all_years)

    for year in range(min_year, max_year + 1):
        income_col = f'present_value_income{year}'
        out_col = f'present_value_out{year}'

        if out_col in df.columns:
            if income_col not in df.columns:
                df[income_col] = 0  # 如果收入列不存在，则默认值为0

            net_present_value[f'net_present_value{year}'] = df[income_col] - df[out_col]

    # 将净现值添加到DataFrame中
    df_net_present_value = pd.DataFrame(net_present_value)

    # 计算累加净现值
    cumulative_net_present_value = df_net_present_value.cumsum(axis=1)
    df_net_present_value = pd.concat([df_net_present_value, cumulative_net_present_value.add_prefix('cumulative_')], axis=1)
    df_net_present_value['well_no'] = data['well_no']

    return df_net_present_value


def add_first_positive_year(df):
    # 获取所有年份列
    year_columns = [col for col in df.columns if col.startswith('cumulative_net_present_value')]

    # 创建一个新的列来存储每个 well_no 第一次出现 cumulative_net_present_value > 0 的年份
    def get_first_positive_year(row):
        for column_name in year_columns:
            if row[column_name] > 0:
                return int(column_name.replace('cumulative_net_present_value', ''))
        return None

    reclaim = df.apply(get_first_positive_year, axis=1)
    return reclaim

def get_highest_production_year(row):
    return row[1:].max()  # Skip the 'well_no' column and return the highest production value

def create_first_year_production_df(year_production_df, df_all_profit):
    # Ensure 'first_production' column exists and is numeric
    if 'first_production' not in year_production_df.columns:
        raise KeyError("The input DataFrame must contain a 'first_production' column.")

    first_year_production = pd.to_numeric(year_production_df['first_production'], errors='coerce').fillna(0)
    first_year_production_df = pd.DataFrame({
        'well_no': year_production_df["well_no"],
        'first_production': first_year_production
    })
    first_year_production_df = pd.merge(
        first_year_production_df, df_all_profit[['well_no', 'construct_year', 'well_type', 'core_area']],
        on='well_no', how='left'
    )
    first_year_production_df = first_year_production_df.dropna(subset=['core_area'])
    return first_year_production_df

def predict_production(first_year_production_df, df_dr):
    min_construct_year = int(first_year_production_df['construct_year'].min())
    max_construct_year = int(first_year_production_df['construct_year'].max())
    prediction_years = range(min_construct_year, max_construct_year + 20)
    production_predictions = []

    for _, row in first_year_production_df.iterrows():
        well_no = row['well_no']
        first_production = row['first_production']
        core_area = row['core_area']
        construct_year = int(row['construct_year'])
        decline_rates = df_dr[core_area].tolist()
        production = []

        for year in prediction_years:
            if year < construct_year or year >= construct_year + 20:
                production.append(0)
            elif year == construct_year:
                production.append(first_production)
            else:
                rate_index = year - construct_year - 1
                rate = float(decline_rates[rate_index])
                production.append(production[-1] * rate)

        production_predictions.append([well_no] + production)

    predict_columns = ['well_no'] + [f'predict_{year}' for year in prediction_years]
    production_predictions_df = pd.DataFrame(production_predictions, columns=predict_columns)
    return production_predictions_df

def calculate_revenue(production_predictions_df, df_fixed_cost, df_all_profit, discount_rate=0.06):
    cumulative_net_presentvalue_columns = [col for col in df_all_profit.columns if 'cumulative_net_present_value' in col]
    current_year = max([int(col.replace('cumulative_net_present_value', '')) for col in cumulative_net_presentvalue_columns])
    production_predictions_df[f'cumulative_net_present_value{current_year}'] = df_all_profit[f'cumulative_net_present_value{current_year}']
    revenues = []

    for _, row in production_predictions_df.iterrows():
        well_no = row['well_no']
        construct_year= row['construct_year']
        revenue = []
        for year in range(current_year, current_year + 20):
            production = row.get(f'predict_{year}', 0)
            price = 1.172592 if year <= 2027 else 1.15056
            discounted_revenue = (production * price) / ((1 + discount_rate) ** (year - current_year))
            fixed_cost_year = int(year - construct_year)
            fixed_cost = df_fixed_cost.loc[df_fixed_cost['year'] == fixed_cost_year, 'fixed_cost'].values[0] if fixed_cost_year in df_fixed_cost['year'].values else 0
            net_revenue = discounted_revenue - fixed_cost
            revenue.append(net_revenue)

        revenues.append([well_no] + revenue)

    revenue_columns = ['well_no'] + [f'revenue_{year}' for year in range(current_year, current_year + 20)]
    revenues_df = pd.DataFrame(revenues, columns=revenue_columns)
    return revenues_df

def calculate_cumulative_net_present_value(revenues_df, df_all_profit, current_year, max_construct_year):
    net_present_years = range(int(current_year)+1, int(max_construct_year) + 21)
    net_present_values = []

    for _, row in revenues_df.iterrows():
        well_no = row['well_no']
        cumulative_net_present_value = [
            df_all_profit.loc[df_all_profit['well_no'] == well_no, f'cumulative_net_present_value{current_year}'].values[0]
        ]

        for year in net_present_years:
            revenue = row.get(f'revenue_{year}', 0)
            cumulative_net_present_value.append(cumulative_net_present_value[-1] + revenue)

        net_present_values.append([well_no] + cumulative_net_present_value[1:])

    columns = ['well_no'] + [f'cumulative_net_present_value{year}' for year in net_present_years]
    net_present_values_df = pd.DataFrame(net_present_values, columns=columns)
    return net_present_values_df

def calculate_first_positive_year(df_combined_profit, first_year_production_df):
    all_years = []

    # 从列名中提取有效的年份
    for col in df_combined_profit.columns:
        if col.startswith('cumulative_net_present_value'):
            try:
                year = int(col[-4:])
                all_years.append(year)
            except ValueError:
                continue  # 跳过后缀不是数字的列

    positive_year = []
    year_difference = []

    for _, row in df_combined_profit.iterrows():
        well_no = row['well_no']
        first_year = None

        # 查找第一个正的净现值年份
        for year in all_years:
            net_present_value = row.get(f'cumulative_net_present_value{year}', None)
            if pd.notna(net_present_value) and net_present_value > 0:
                first_year = year
                break

        if first_year is None:
            first_year = '20年以上'
            year_diff = '20年以上'
        else:
            construct_year_row = first_year_production_df.loc[first_year_production_df['well_no'] == well_no, 'construct_year']
            if not construct_year_row.empty:
                construct_year = construct_year_row.values[0]
                year_diff = first_year - construct_year
            else:
                construct_year = '未知'
                year_diff = '未知'

        positive_year.append(first_year)
        year_difference.append(year_diff)

    df_combined_profit['first_positive_year'] = positive_year
    df_combined_profit['year_difference'] = year_difference

    return df_combined_profit


def set_exceeded_production_to_none(df_combined_profit, production_years=20):
    """
    对于每口气井，将超过其生产周期的 cumulative_net_present_value 设为 None。

    参数：
    df_combined_profit: DataFrame，包含累计净现值的数据和每口井的建井年份等信息。
    production_years: int，生产周期长度，默认为 20 年。

    返回：
    DataFrame，更新后的累计净现值数据。
    """
    # 获取所有累计净现值的年份列
    year_columns = [col for col in df_combined_profit.columns if col.startswith('cumulative_net_present_value')]

    # 遍历每口井并修改超过生产周期的年份的累计净现值
    for _, row in df_combined_profit.iterrows():
        well_no = row['well_no']
        construct_year = row['construct_year']
        production_end_year = construct_year + production_years

        # 将超过生产周期的累计净现值设为 None
        for year_col in year_columns:
            year = int(year_col.replace('cumulative_net_present_value', ''))
            if year > production_end_year:
                df_combined_profit.loc[df_combined_profit['well_no'] == well_no, year_col] = None

    return df_combined_profit

def main():
    pass

if __name__ == '__main__':
    main()
